from typing import List, Optional, Dict, Any
from .db import get_db
import sqlite3

# 考勤类型相关操作
def get_all_attendance_types() -> List[Dict[str, Any]]:
    """获取所有考勤类型"""
    with get_db() as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM attendance_types")
        columns = [description[0] for description in cursor.description]
        return [dict(zip(columns, row)) for row in cursor.fetchall()]


def get_attendance_type(type_id: int) -> Optional[Dict[str, Any]]:
    """获取指定考勤类型"""
    with get_db() as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM attendance_types WHERE type_id = ?", (type_id,))
        row = cursor.fetchone()
        if row:
            columns = [description[0] for description in cursor.description]
            return dict(zip(columns, row))
        return None


def create_attendance_type(type_data: Dict[str, Any]) -> Dict[str, Any]:
    """创建新考勤类型"""
    with get_db() as conn:
        cursor = conn.cursor()
        cursor.execute("""
            INSERT INTO attendance_types (type_id, type_name)
            VALUES (?, ?)
        """, (type_data["type_id"], type_data["type_name"]))
        conn.commit()
        return get_attendance_type(type_data["type_id"])


def update_attendance_type(type_id: int, type_data: Dict[str, Any]) -> Optional[Dict[str, Any]]:
    """更新考勤类型"""
    with get_db() as conn:
        cursor = conn.cursor()
        cursor.execute("""
            UPDATE attendance_types SET type_name = ?
            WHERE type_id = ?
        """, (type_data["type_name"], type_id))
        conn.commit()
        return get_attendance_type(type_id)


def batch_create_attendance_types(types: List[Dict[str, Any]]) -> List[Dict[str, Any]]:
    """批量创建考勤类型"""
    created_types = []
    with get_db() as conn:
        cursor = conn.cursor()
        try:
            # 开始事务
            cursor.execute("BEGIN TRANSACTION")

            # 准备批量插入的数据
            values = []
            for type_data in types:
                values.append((
                    type_data["type_id"],
                    type_data["type_name"]
                ))

            # 批量插入数据
            cursor.executemany("""
                INSERT INTO attendance_types (type_id, type_name)
                VALUES (?, ?)
            """, values)

            # 获取新创建的记录
            type_ids = [type_data["type_id"] for type_data in types]
            placeholders = ','.join(['?'] * len(type_ids))
            cursor.execute(f"""
                SELECT * FROM attendance_types 
                WHERE type_id IN ({placeholders})
            """, type_ids)

            # 将结果转换为字典列表
            columns = [description[0] for description in cursor.description]
            created_types = [dict(zip(columns, row)) for row in cursor.fetchall()]

            # 提交事务
            conn.commit()

        except sqlite3.IntegrityError as e:
            # 回滚事务
            conn.rollback()
            print(f"数据完整性错误: {str(e)}")
            raise e
        except Exception as e:
            # 回滚事务
            conn.rollback()
            print(f"意外错误: {str(e)}")
            raise e

    return created_types
